﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
//C#操作数据库
namespace 数据库
{
    public partial class Form1 : Form
    {
        string constr = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=D:\比特就业课\代码仓库区\data-structure\数据库\数据库\Properties\Database1.mdf;Integrated Security=True";
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            //连接字符串
            
            SqlConnection con= new SqlConnection(constr);
            con.Open();
            MessageBox.Show("连接已打开");
            //关闭连接
            con.Close();
        }

        private void button2_Click(object sender, EventArgs e)
        {
            SqlConnection con = new SqlConnection(constr);
            //1、sql语句查询
             string sql = "insert into tTable (uname.password) values (N'李四','123456')";
             /*SqlCommand cmd = new SqlCommand();
             cmd.Connection = con1;
             cmd.CommandText = sql;*/
            SqlCommand cmd = new SqlCommand(sql, con);
            try
            {
                con.Open();
                int n = cmd.ExecuteNonQuery();
                MessageBox.Show(n.ToString());
            }
            catch(Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally//一定要执行的语句就放进这里
            {
                con.Close();//数据库连接关闭

            }
        }

        private void button3_Click(object sender, EventArgs e)
        {
            //查询命令
            SqlConnection con = new SqlConnection(constr);
            //1、sql语句查询
            string sql = "insert into tTable (uname.password) values (N'李四','123456').select @@identity";
            /*SqlCommand cmd = new SqlCommand();
            cmd.Connection = con1;
            cmd.CommandText = sql;*/
            SqlCommand cmd = new SqlCommand(sql, con);
            try
            {
                con.Open();
                int n = Convert.ToInt32(cmd.ExecuteScalar());
                MessageBox.Show("人数" + n);
            }
            catch(Exception e1)
            {
                MessageBox.Show(e1.Message);
            }
            finally
            {
                con.Close();
            }
   

        }

        private void listBox1_SelectedIndexChanged(object sender, EventArgs e)
        {
            SqlConnection con = new SqlConnection(constr);
            //1、sql语句查询
            string sql = "select * fron tTable";
            SqlCommand cmd = con.CreateCommand();
            cmd.CommandText = sql;
            con.Open();
            //执行sql命令
            SqlDataReader reader= cmd.ExecuteReader();
            while(reader.Read())
            {
                string name=reader.GetString(1);
                //Id
                int id= reader.GetInt32(reader.GetOrdinal("Id"));
                string psd = reader["password"].ToString();
                string str = id + "  " + name + "  " + psd;
                listBox1.Items.Add(str);
            }
            reader.Close();
            con.Close();

        }
    }
}
//Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=D:\比特就业课\代码仓库区\data-structure\数据库\数据库\Properties\Database1.mdf;Integrated Security=True